﻿IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sp_Report_BanHangVaThuTienTheoNgay]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_Report_BanHangVaThuTienTheoNgay]
GO


CREATE PROC [sp_Report_BanHangVaThuTienTheoNgay]
(
	@Ma_chi_nhanh 		INT,
	@Ngay_xem 			DATETIME
)	
AS
BEGIN
	/* 	
	* declare bien @Dau_thang de lay day du ngay dau tien cua thang,	
	* declare bien @Tong_tien_toan_chi_nhanh de luu tong tien cua toan chi nhanh trong thang do, SUM() cot Tong_tien cua bang PhieuNhapXuat,
	* declare bien @Thuc_thu_toan_chi_nhanh de luu tong tien khach thanh toan cua toan chi nhanh trong thang do, SUM() cot Thanh_toan cua bang PhieuNhapXuat,
	* tat ca dua vao ma chi nhanh va ngay xem
	*/
	DECLARE @Doanh_so					NVARCHAR(50)
	DECLARE @Thuc_thu					NVARCHAR(50)	 
	DECLARE @Dau_thang					DATETIME
	DECLARE @Tong_tien_toan_chi_nhanh	INT
	DECLARE @Thuc_thu_toan_chi_nhanh	INT
	DECLARE @Ngay_xem_in				NVARCHAR(10)	

	SET @Doanh_so = N'A - Doanh số bán hàng'
	SET @Thuc_thu = N'B - Thực thu tiền'
	SET @Dau_thang = DATEADD(dd,-(DAY(DATEADD(mm,1,@Ngay_xem))-1),DATEADD(mm,0,@Ngay_xem))
	SET @Ngay_xem_in = CONVERT(NVARCHAR, DAY(@Ngay_xem)) + '/' + CONVERT(NVARCHAR, MONTH(@Ngay_xem)) + '/' + CONVERT(NVARCHAR, YEAR(@Ngay_xem))

	SET @Tong_tien_toan_chi_nhanh = (SELECT SUM(CASE 
													WHEN CONVERT(NVARCHAR,B.Ngay_cap_nhat_cuoi,111) BETWEEN @Dau_thang AND @Ngay_xem 
													THEN B.Thanh_tien 
													ELSE 0 
												END)
			FROM ThanhVien AS A INNER JOIN PhieuNhapXuat AS B ON A.id = B.Ma_nhan_vien			 
			WHERE A.Ma_chi_nhanh = @Ma_chi_nhanh AND B.Loai_nhap_xuat = 0)

	SET @Thuc_thu_toan_chi_nhanh = (SELECT SUM(CASE 
													WHEN CONVERT(NVARCHAR,B.Ngay_cap_nhat_cuoi,111) BETWEEN @Dau_thang AND @Ngay_xem
													THEN B.Thanh_toan 
													ELSE 0 
												END)
			FROM ThanhVien AS A INNER JOIN PhieuNhapXuat AS B ON A.id = B.Ma_nhan_vien			 
			WHERE A.Ma_chi_nhanh = @Ma_chi_nhanh AND B.Loai_nhap_xuat = 0)
	
	/*
	* lay ho ten cua tat ca nhan vien,
	* tinh tong tien trong ngay moi nhan vien ban duoc,
	* luy ke = tinh tong tien trong thang co ngay do ma nhan vien ban duoc
	* ti le = tong so tien ban duoc cua moi nhan vien * 100 / tong so tien ban duoc trong ca chi nhanh (tat ca tinh trong thang)
	* dua vao ngay xem va chi nhanh
	*/		
	/* 
	* neu @Tong_tien_toan_chi_nhanh hoac @Thuc_thu_toan_chi_nhanh = 0 tuc la khong co thu chi nen tat ca cac cot duoc set = 0 
	*/
	BEGIN
		IF @Tong_tien_toan_chi_nhanh = 0 OR @Thuc_thu_toan_chi_nhanh = 0
		BEGIN
			SELECT A.Ho_ten AS 'Ho_ten', 
				'Tong_tien_trong_ngay' = 0, 
				'Tong_tien_trong_thang' = 0, 
				'Ti_le' = 0,
				'Phan_loai' = @Doanh_so,
				'Ngay_xem' = @Ngay_xem_in 
			FROM ChiTietThanhVien AS A INNER JOIN ThanhVien AS B ON A.Ma_thanh_vien = B.id
			WHERE B.Ma_chi_nhanh = @Ma_chi_nhanh
			UNION ALL
			SELECT A.Ho_ten, 
				0, 
				0, 
				0,
				@Thuc_thu, 
				@Ngay_xem_in
			FROM ChiTietThanhVien AS A INNER JOIN ThanhVien AS B ON A.Ma_thanh_vien = B.id
			WHERE B.Ma_chi_nhanh = @Ma_chi_nhanh
		END

		ELSE
		BEGIN
			SELECT A.Ho_ten AS 'Ho_ten',
				SUM(CASE 
						WHEN CONVERT(NVARCHAR,C.Ngay_cap_nhat_cuoi,111) = @Ngay_xem AND C.Loai_nhap_xuat = 0
						THEN C.Thanh_tien 
						ELSE 0 
					END) AS 'Tong_tien_trong_ngay', 
				SUM(CASE 
						WHEN CONVERT(NVARCHAR,C.Ngay_cap_nhat_cuoi,111) BETWEEN @Dau_thang AND @Ngay_xem AND C.Loai_nhap_xuat = 0
						THEN C.Thanh_tien 
						ELSE 0 
					END) AS 'Tong_tien_trong_thang',
				ROUND((SUM(CASE 
								WHEN CONVERT(NVARCHAR,C.Ngay_cap_nhat_cuoi,111) BETWEEN @Dau_thang AND @Ngay_xem AND C.Loai_nhap_xuat = 0
								THEN C.Thanh_tien 
								ELSE 0 
							END)*100/@Tong_tien_toan_chi_nhanh), 2) AS 'Ti_le',
				'Phan_loai' = @Doanh_so,
				'Ngay_xem' = @Ngay_xem_in  
			FROM ChiTietThanhVien AS A INNER JOIN ThanhVien AS B ON A.Ma_thanh_vien = B.id
				LEFT OUTER JOIN PhieuNhapXuat AS C ON C.Ma_nhan_vien = B.id
			WHERE B.Ma_chi_nhanh = @Ma_chi_nhanh
			GROUP BY A.Ho_ten
			UNION ALL
			SELECT A.Ho_ten,
				SUM(CASE 
						WHEN CONVERT(NVARCHAR,C.Ngay_cap_nhat_cuoi,111) = @Ngay_xem AND C.Loai_nhap_xuat = 0
						THEN C.Thanh_toan 
						ELSE 0 
					END), 
				SUM(CASE 
						WHEN CONVERT(NVARCHAR,C.Ngay_cap_nhat_cuoi,111) BETWEEN @Dau_thang AND @Ngay_xem AND C.Loai_nhap_xuat = 0
						THEN C.Thanh_toan 
						ELSE 0 
					END),
				ROUND((SUM(CASE 
								WHEN CONVERT(NVARCHAR,C.Ngay_cap_nhat_cuoi,111) BETWEEN @Dau_thang AND @Ngay_xem AND C.Loai_nhap_xuat = 0
								THEN C.Thanh_toan 
								ELSE 0 
							END)*100/@Thuc_thu_toan_chi_nhanh), 2),
				@Thuc_thu, 
				@Ngay_xem_in
			FROM ChiTietThanhVien AS A INNER JOIN ThanhVien AS B ON A.Ma_thanh_vien = B.id
				LEFT OUTER JOIN PhieuNhapXuat AS C ON C.Ma_nhan_vien = B.id
			WHERE B.Ma_chi_nhanh = @Ma_chi_nhanh
			GROUP BY A.Ho_ten
		END
	END
	
END


